﻿Imports Microsoft.VisualBasic
Imports Aras.IOM
Imports MSXML2
Imports System.Xml
Imports System.IO



Public Class ConnecteurAras

    Dim conn As HttpServerConnection
    Dim inn As Innovator

    Public Sub New(ByVal url As String, _
                   ByVal db As String, _
                   ByVal InnovUser As String, _
                   ByVal pass As String)



        pass = Innovator.ScalcMD5(pass)
        conn = IomFactory.CreateHttpServerConnection(url, db, InnovUser, pass)
        Dim login_result As Item = conn.Login


        If login_result.isError Then
            Throw New Exception("login failed")
        End If

        inn = IomFactory.CreateInnovator(conn)

    End Sub


    Public Function getfichiers(ByVal IDfolder As String) As String


        Dim qryitem As Item = Me.inn.newItem("DossierCollab", "get")
        qryitem.setProperty("id", IDfolder)
        Dim rel1 As Item = Me.inn.newItem("DossierDocument", "get")
        Dim subfolder1 As Item = Me.inn.newItem("Document", "get")
        subfolder1.setAttribute("select", " keyed_name, major_rev,name , state, classification")

        Dim phraseretour As String = "test"
        rel1.setRelatedItem(subfolder1)
        qryitem.addRelationship(rel1)

        Dim results As Item = qryitem.apply()

        Dim traitementXML As DOMDocument = New DOMDocument

        phraseretour = ""
        phraseretour += "<?xml version='1.0' encoding='utf-8' ?><rows>"
        Dim a As Integer
        Dim countsub As Integer = 0

        Dim item As Item = results.getItemByIndex(0)
        Try

            countsub = item.getRelationships.getItemCount

        Catch ex As Exception
            GoTo NoFichier
        End Try
        For a = 0 To countsub - 1
            Dim subitem1 As Item = item.getRelationships.getItemByIndex(a)
            Dim subitem2 As Item = subitem1.getRelatedItem.getItemByIndex(0)

            phraseretour += "<row id='" & a & "'>"
            phraseretour += "<cell>" & subitem2.getProperty("keyed_name") & "</cell>"
            phraseretour += "<cell>" & subitem2.getProperty("major_rev") & "</cell>"
            phraseretour += "<cell>" & subitem2.getProperty("name") & "</cell>"
            phraseretour += "<cell>" & subitem2.getProperty("classification") & "</cell>"
            phraseretour += "<cell>" & subitem2.getProperty("state") & "</cell>"
            phraseretour += "</row>"

        Next



        'Dim writer As XmlTextWriter = New XmlTextWriter("TempXML/grid_" & IDfolder & ".xml", Nothing)
        'Dim fsout As IO.FileStream = New IO.FileStream("step4.xml", FileMode.Create)
        'Dim chemin As String = System.AppDomain.CurrentDomain.BaseDirectory()
        'Dim writer As XmlTextWriter = New XmlTextWriter(chemin & "/TempXML/grid_" & IDfolder & ".xml", System.Text.Encoding.UTF8)
        'writer.Formatting = Formatting.Indented
        'Dim doc As XmlDocument = New XmlDocument
        'doc.LoadXml(phraseretour)
        'doc.save(writer)
NoFichier:
        phraseretour += "</rows>"
        Return phraseretour


    End Function


    Public Function getitemtypes(ByVal isrelationship As Boolean, ByVal withmorphae As Boolean) As Item

        Accueil.Timer1.Start()
        Accueil.Timer1.Interval = 300
        Dim RSparameter As String
        If isrelationship = True Then
            RSparameter = "1"
        Else
            RSparameter = "0"
        End If




        Dim qryitem As Item = Me.inn.newItem("ItemType", "get")
        qryitem.setProperty("is_relationship", RSparameter)

        If withmorphae = True Then
            Dim subrel As Item = Me.inn.newItem("Morphae", "get")
            Dim subitemtype As Item = Me.inn.newItem("ItemType", "get")
            subrel.setRelatedItem(subitemtype)
            qryitem.addRelationship(subrel)
        End If


        Dim results As Item = qryitem.apply()

        Accueil.Timer1.Stop()
        Accueil.ProgressBar1.Value = 30
        Return results



    End Function

    Public Function getItemProperties(ByVal tablename As String) As Item

        Dim Answer As Item
        Dim SQL As String
        SQL = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS  where TABLE_NAME= '" & tablename & "'"
        Answer = Me.inn.applySQL(SQL)

        Return Answer

    End Function

    Public Function getPropertiesLinkOfTypeItem(ByVal tablename As String) As String(,)

        Dim Answer, Answer2, Answer3, Answer4 As Item
        Dim Resultat(20, 2) As String
        Dim SQL, SQL2, SQL3, SQL4 As String
        Dim PropertyQty, i As Integer

        'première requete sur la structure de l'item type pour récupérer les noms des propriétés de type Item
        SQL = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS  where TABLE_NAME= '" & tablename & "' and DATA_TYPE='char' and CHARACTER_MAXIMUM_LENGTH='32'"
        Answer = Me.inn.applySQL(SQL)
        Try

            PropertyQty = Answer.nodeList.Count
        Catch ex As Exception

            If Answer.node Is Nothing Then
                PropertyQty = 0
            Else
                PropertyQty = 1
            End If

        End Try
        ReDim Resultat(PropertyQty - 1, 1)

        SQL2 = "SELECT id from innovator.ITEMTYPE where innovator.ITEMTYPE.INSTANCE_DATA= '" & tablename & "'"
        Answer2 = Me.inn.applySQL(SQL2)

        'pour chaque resultat de SQL boucler
        For i = 0 To PropertyQty - 1
            Try
                ' seconde requete pour récupérer pour chaque propriété de type Item le source id de la propriété


                SQL3 = "SELECT DATA_SOURCE from innovator.PROPERTY where SOURCE_ID= '" & Answer2.node.InnerText & "' AND NAME='" & Answer.nodeList.Item(i).InnerText & "'"
                Answer3 = Me.inn.applySQL(SQL3)

                SQL4 = "select name from innovator.ITEMTYPE where ID='" & Answer3.node.InnerText & "'"
                Answer4 = Me.inn.applySQL(SQL4)
                Resultat(i, 0) = Answer4.node.InnerText
                Resultat(i, 1) = Answer.nodeList.Item(i).InnerText
            Catch ex As Exception
                Resultat(i, 0) = "Erreur"
                Resultat(i, 1) = "Erreur"
            End Try

        Next


        Return Resultat

    End Function


    Public Function getRelationshiptypes()

        Dim qryitem As Item = Me.inn.newItem("RelationshipType", "get")
        Dim results As Item = qryitem.apply()

        Return results

    End Function


    Public Function addfile(ByVal IDfolder As String, ByVal FileName As String) As String
        Dim result As String = ""

        Dim qryitem As Item = Me.inn.newItem("DossierCollab", "edit")
        qryitem.setAttribute("select", "nomdossier")
        qryitem.setAttribute("where", "[DossierCollab].id='" & IDfolder & "'")
        Dim rel As Item = Me.inn.newItem("DossierDocument", "add")
        Dim subitem As Item = Me.inn.newItem("Document", "add")
        subitem.setProperty("FileName", FileName)



        rel.setRelatedItem(subitem)
        qryitem.addRelationship(rel)

        Dim results As Item = qryitem.apply()

        'test si existance d'une erreur
        result = Me.getdossiers()


        Return result

    End Function



    Public Function getdossiers() As String

        Dim qryitem As Item = Me.inn.newItem("DossierCollab", "get")
        qryitem.setAttribute("select", "nomdossier")
        qryitem.setProperty("isrootfolder", "1")
        Dim rel1 As Item = Me.inn.newItem("DossierSousDos", "get")
        Dim subfolder1 As Item = Me.inn.newItem("DossierCollab", "get")
        Dim rel2 As Item = Me.inn.newItem("DossierSousDos", "get")
        Dim subfolder2 As Item = Me.inn.newItem("DossierCollab", "get")
        Dim rel3 As Item = Me.inn.newItem("DossierSousDos", "get")
        Dim subfolder3 As Item = Me.inn.newItem("DossierCollab", "get")
        Dim rel4 As Item = Me.inn.newItem("DossierSousDos", "get")
        Dim subfolder4 As Item = Me.inn.newItem("DossierCollab", "get")
        Dim rel5 As Item = Me.inn.newItem("DossierSousDos", "get")
        Dim subfolder5 As Item = Me.inn.newItem("DossierCollab", "get")
        Dim rel6 As Item = Me.inn.newItem("DossierSousDos", "get")
        Dim subfolder6 As Item = Me.inn.newItem("DossierCollab", "get")
        rel6.setRelatedItem(subfolder6)
        subfolder5.addRelationship(rel6)
        rel5.setRelatedItem(subfolder5)
        subfolder4.addRelationship(rel5)
        rel4.setRelatedItem(subfolder4)
        subfolder3.addRelationship(rel4)
        rel3.setRelatedItem(subfolder3)
        subfolder2.addRelationship(rel3)
        rel2.setRelatedItem(subfolder2)
        subfolder1.addRelationship(rel2)
        rel1.setRelatedItem(subfolder1)
        qryitem.addRelationship(rel1)
        Dim results As Item = qryitem.apply()





        Dim count As Integer = results.getItemCount
        Dim a, b, c, d, e, f, g As Integer

        Dim content As String = "<?xml version='1.0' ?><tree id='0'>"
        Dim countsub1, countsub2, countsub4, countsub6 As Integer


        For a = 0 To count - 1
            Dim item As Item = results.getItemByIndex(a)
            content += "" & _
            "<item id='" & item.getProperty("id") & "' text='" & item.getProperty("nomdossier") & "'>"
            countsub1 = item.getRelationships.getItemCount
            For b = 0 To countsub1 - 1
                Dim subitem1 As Item = item.getRelationships.getItemByIndex(b)
                countsub2 = subitem1.getItemCount
                Dim subitem2 As Item = subitem1.getRelatedItem.getItemByIndex(0)
                content += "" & _
                "<item id='" & subitem2.getProperty("id") & "' text='" & subitem2.getProperty("nomdossier") & "'>"
                countsub2 = subitem2.getRelationships.getItemCount
                For c = 0 To countsub2 - 1
                    Dim subitem3 As Item = subitem2.getRelationships.getItemByIndex(d)
                    countsub4 = subitem3.getItemCount
                    Dim subitem4 As Item = subitem3.getRelatedItem.getItemByIndex(0)
                    content += "" & _
                    "<item id='" & subitem4.getProperty("id") & "' text='" & subitem4.getProperty("nomdossier") & "'>"
                    countsub4 = subitem4.getRelationships.getItemCount
                    For e = 0 To countsub4 - 1
                        Dim subitem5 As Item = subitem4.getRelationships.getItemByIndex(f)
                        countsub6 = subitem5.getItemCount
                        Dim subitem6 As Item = subitem5.getRelatedItem.getItemByIndex(g)
                        content += "" & _
                        "<item id='" & subitem6.getProperty("id") & "' text='" & subitem6.getProperty("nomdossier") & "'>"

                        content += "" & _
                        "</item>"
                    Next
                    content += "" & _
                    "</item>"
                Next
                content += "" & _
                "</item>"
            Next
            content += "" & _
            "</item>"
        Next


        content = content & "</tree>"
        Return content
    End Function


    Public Function newfolder(ByVal IDfolder As String, ByVal foldername As String, ByVal isroot As Boolean) As String
        Dim result As String = ""

        Dim qryitem As Item = Me.inn.newItem("DossierCollab", "edit")
        qryitem.setAttribute("select", "nomdossier")
        qryitem.setAttribute("where", "[DossierCollab].id='" & IDfolder & "'")
        Dim rel As Item = Me.inn.newItem("DossierSousDos", "add")
        Dim subitem As Item = Me.inn.newItem("DossierCollab", "add")
        subitem.setProperty("nomdossier", foldername)

        If isroot = False Then
            subitem.setProperty("isrootfolder", "0")
        Else
            subitem.setProperty("isrootfolder", "1")
        End If

        rel.setRelatedItem(subitem)
        qryitem.addRelationship(rel)

        Dim results As Item = qryitem.apply()

        'test si existance d'une erreur
        result = Me.getdossiers()


        Return result
    End Function

    Protected Overrides Sub Finalize()
        Try

            conn.Logout()
        Catch ex As Exception
            GoTo fin
        End Try
fin:
        MyBase.Finalize()
    End Sub
End Class
